﻿CREATE FUNCTION dbo.fs_StatePages
( @StateID int,
	@FramePage bit
)
RETURNS @Ret TABLE (PageID int, PageDesc nvarchar(128),PageUrl varchar(256),ParentStateID int,TabIndex int)
AS BEGIN

DECLARE @T TABLE (PageID int, PageDesc nvarchar(128),PageUrl varchar(256),ParentStateID int,TabIndex int)
DECLARE @PageTypeID int


INSERT INTO @T
SELECT PageID, PageDesc, PageURL,ParentStateID,TabIndex
FROM  v_State_Pages_NameSpace
WHERE (StateID = @StateID) AND
			(@FramePage = 1 AND PageTypeID = 9 OR @FramePage = 0 AND ISNULL(PageTypeID,0) <> 9)

IF @FramePage = 0
BEGIN

SELECT @StateID = ParentStateID FROM fs_StatePages(@StateID,1)

IF @@ROWCOUNT > 0
	INSERT INTO @T
	SELECT PageID, PageDesc, PageURL,ParentStateID,TabIndex FROM  fs_StatePages(@StateID,@FramePage)

END

INSERT INTO @Ret
SELECT * FROM @T ORDER BY TabIndex

RETURN
END

